#!/usr/bin/env python3
"""
数据库连接监控脚本
定期检查MySQL连接数并记录日志
"""

import os
import sys
import time
import logging
from datetime import datetime

# 添加项目路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from services.shared.db_pool import get_db_session, close_db_session

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('/tmp/db_connection_monitor.log'),
        logging.StreamHandler()
    ]
)

logger = logging.getLogger(__name__)

def check_db_connections():
    """检查数据库连接数"""
    session = None
    try:
        session = get_db_session()
        
        # 查询当前连接数
        result = session.execute(
            "SHOW STATUS LIKE 'Threads_connected'"
        ).fetchone()
        
        if result:
            current_connections = int(result[1])
            
            # 查询活跃连接数
            active_result = session.execute(
                "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep'"
            ).fetchone()
            
            active_connections = int(active_result[0]) if active_result else 0
            
            # 记录日志
            logger.info(f"总连接数: {current_connections}, 活跃连接数: {active_connections}")
            
            # 如果连接数过高，发出警告
            if current_connections > 200:
                logger.warning(f"连接数过高: {current_connections}/300")
            elif current_connections > 100:
                logger.info(f"连接数正常但需关注: {current_connections}/300")
            else:
                logger.info(f"连接数正常: {current_connections}/300")
                
            return current_connections, active_connections
        else:
            logger.error("无法获取连接数信息")
            return None, None
            
    except Exception as e:
        logger.error(f"检查数据库连接时出错: {e}")
        return None, None
    finally:
        if session:
            close_db_session(session)

def cleanup_idle_connections():
    """清理空闲连接"""
    session = None
    try:
        session = get_db_session()
        
        # 查询空闲时间超过30分钟的连接
        idle_connections = session.execute(
            "SELECT ID FROM information_schema.PROCESSLIST WHERE COMMAND = 'Sleep' AND TIME > 1800"
        ).fetchall()
        
        if idle_connections:
            logger.info(f"发现 {len(idle_connections)} 个长时间空闲连接")
            
            # 注意：在生产环境中谨慎使用KILL命令
            # for conn in idle_connections:
            #     session.execute(f"KILL {conn[0]}")
            #     logger.info(f"已终止空闲连接: {conn[0]}")
        else:
            logger.info("没有发现长时间空闲连接")
            
    except Exception as e:
        logger.error(f"清理空闲连接时出错: {e}")
    finally:
        if session:
            close_db_session(session)

def main():
    """主函数"""
    logger.info("开始数据库连接监控")
    
    while True:
        try:
            # 检查连接数
            total_conn, active_conn = check_db_connections()
            
            # 每小时清理一次空闲连接
            current_time = datetime.now()
            if current_time.minute == 0:  # 每小时的0分钟执行
                cleanup_idle_connections()
            
            # 等待5分钟
            time.sleep(300)
            
        except KeyboardInterrupt:
            logger.info("监控程序被用户中断")
            break
        except Exception as e:
            logger.error(f"监控程序出错: {e}")
            time.sleep(60)  # 出错后等待1分钟再重试

if __name__ == "__main__":
    main()